package zmee.pur.src.report;

import kd.bos.algo.DataSet;
import kd.bos.dataentity.entity.DynamicObject;
import kd.bos.db.DB;
import kd.bos.db.DBRoute;
import kd.bos.entity.report.AbstractReportListDataPlugin;
import kd.bos.entity.report.FilterInfo;
import kd.bos.entity.report.ReportQueryParam;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Author: xw
 * @Date: 2023/01/12/15:37
 * @Description:
 */
public class PurSituationRptListDataPlugin extends AbstractReportListDataPlugin {


    @Override
    public DataSet query(ReportQueryParam reportQueryParam, Object o) throws Throwable {
        FilterInfo filter = reportQueryParam.getFilter();
        DynamicObject org = filter.getDynamicObject("zmee_org");
        List<Object> params = new ArrayList<Object>();
        params.add(org.get("id"));
        Date startTime = filter.getDate("zmee_starttime");
        Date endTime = filter.getDate("zmee_endtime");
        StringBuilder timeWhere = new StringBuilder();
        if (startTime != null && endTime != null) {
            String startDate = formattedDateToyyMMdd(startTime);
            String entDate = formattedDateToyyMMdd(endTime);
            timeWhere.append(" AND DATE_FORMAT( t2.FAUDITDATE, '%y-%m-%d' )>='").append(startDate).append("'");
            timeWhere.append(" AND DATE_FORMAT( t2.FAUDITDATE, '%y-%m-%d' )<='").append(entDate).append("'");
        }
        DynamicObject project = filter.getDynamicObject("zmee_project");
        if (project != null) {
            Object id = project.get("id");
            timeWhere.append("AND t1.FID='").append(id).append("'");
        }
        String sql = "/*dialect*/SELECT\n" +
                "\tt1.FID,\n" +
                "\tDATE_FORMAT( t2.FAUDITDATE, '%Y年%m月' ) AS zmee_month,\n" +
                "\tt1.FBIDNAME AS zmee_bidname,\n" +
                "\tt1.ffmoney / 10000 AS zmee_money,\n" +
                "\tt1.FSOURCETYPEID AS zmee_sourcetype,\n" +
                "\tt1.FBIZTYPEID AS zmee_biztype,\n" +
                "\t( CASE WHEN t4.FSUPSCOPE = '1' THEN t4.FBILLDATE ELSE '/' END ) AS zmee_billdate,\n" +
                "\tt1.FSTOPBIDDATE AS zmee_stopbiddate,\n" +
                "\tt5.zmee_supplier,\n" +
                "\tt8.zmee_judgeuser,\n" +
                "\tt6.zmee_bidwinner,\n" +
                "\t( CASE WHEN t9.fparentid = '1577457714424979456' THEN t6.zmee_bidtaxamount WHEN t9.fparentid = '1577458386067266560' THEN t6.zmee_bidtaxamount ELSE zmee_bidamount END ) AS zmee_bidamount,\n" +
                "\tt10.zmee_contractno,\n" +
                "\tt10.zmee_signdate,\n" +
                "\tt10.zmee_originaloftaxamount,\n" +
                "\tt10.zmee_dateeas,\n" +
                "\tt10.zmee_contac,\n" +
                "\t(\n" +
                "\tCASE\n" +
                "\t\t\tWHEN t1.fk_zmee_noticway = '1' THEN\n" +
                "\t\t\t'物产环能网站' \n" +
                "\t\t\tWHEN t1.fk_zmee_noticway = '2' THEN\n" +
                "\t\t\t'物产中大网站' \n" +
                "\t\t\tWHEN t1.fk_zmee_noticway = '3' THEN\n" +
                "\t\t\t'中国招标投标公共服务平台' \n" +
                "\t\t\tWHEN t1.fk_zmee_noticway = '4' THEN\n" +
                "\t\t\t'无' ELSE '' \n" +
                "\t\tEND \n" +
                "\t\t) AS zmee_noticway,\n" +
                "\t\tGROUP_CONCAT( t9.fname ) AS zmee_bdprojectname,\n" +
                "\t\tGROUP_CONCAT( t9.kind ) AS zmee_projectkind \n" +
                "\tFROM\n" +
                "\t\tt_src_project t1\n" +
                "\t\tLEFT JOIN t_src_project_t t2 ON t1.FID = t2.FID\n" +
                "\t\tLEFT JOIN t_pur_notice_a t3 ON t1.FID = t3.FSRCBILLID\n" +
                "\t\tLEFT JOIN t_pur_notice t4 ON t4.FID = t3.FID\n" +
                "\t\tLEFT JOIN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt1.FID,\n" +
                "\t\t\tCONCAT(\n" +
                "\t\t\t\tCONCAT(\n" +
                "\t\t\t\t\t'应标：',\n" +
                "\t\t\t\tGROUP_CONCAT( CONCAT( '【',( CASE WHEN t2.FISCONFIRM = 1 THEN t3.FNAME ELSE '' END ), '】' ) SEPARATOR ';' )),\n" +
                "\t\t\tCONCAT(\n" +
                "\t\t\t\t'投标：',\n" +
                "\t\t\t\tGROUP_CONCAT( CONCAT( '【',( CASE WHEN t2.FISTENDER = 1 THEN t3.FNAME ELSE NULL END ), '】' ) SEPARATOR ';' ) \n" +
                "\t\t\t) \n" +
                "\t\t) AS zmee_supplier \n" +
                "\t\tFROM\n" +
                "\t\t\tt_src_project t1\n" +
                "\t\t\tLEFT JOIN t_src_invitesupplier t2 ON t1.FID = t2.FID\n" +
                "\t\t\tLEFT JOIN zmee_eip.T_BD_Supplier t3 ON t3.FID = t2.FSUPPLIERID \n" +
                "\t\tGROUP BY\n" +
                "\t\t\tt1.FID \n" +
                "\t\t) t5 ON t1.FID = t5.FID\n" +
                "\t\tLEFT JOIN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt1.FID,\n" +
                "\t\t\tGROUP_CONCAT( t3.FNAME ) AS zmee_bidwinner,\n" +
                "\t\t\tGROUP_CONCAT(0+CAST( ROUND( t2.FTAXAMOUNT / 10000, 4 ) AS CHAR )) AS zmee_bidtaxamount,\n" +
                "\t\t\tGROUP_CONCAT(0+CAST( ROUND( t2.FAMOUNT / 10000, 4 ) AS CHAR )) AS zmee_bidamount\n" +
                "\t\tFROM\n" +
                "\t\t\tt_src_project t1\n" +
                "\t\t\tLEFT JOIN t_src_decisionsumsup t2 ON t1.FID = t2.FPARENTID\n" +
                "\t\t\tLEFT JOIN zmee_eip.T_BD_Supplier t3 ON t2.FSUPPLIERID = t3.FID \n" +
                "\t\tGROUP BY\n" +
                "\t\t\tt1.FID \n" +
                "\t\t) t6 ON t1.FID = t6.FID\n" +
                "\t\tLEFT JOIN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt1.FID,\n" +
                "\t\t\tGROUP_CONCAT( t3.FTRUENAME ) AS zmee_judgeuser \n" +
                "\t\tFROM\n" +
                "\t\t\tt_src_project t1\n" +
                "\t\t\tLEFT JOIN fjudgeuser t2 ON t1.FID = t2.FID\n" +
                "\t\t\tLEFT JOIN zmee_eip.t_SEC_User t3 ON t2.FBasedataId = t3.FID \n" +
                "\t\tGROUP BY\n" +
                "\t\t\tt1.FID \n" +
                "\t\t) t8 ON t1.FID = t8.FID\n" +
                "\t\tLEFT JOIN (\n" +
                "\t\tSELECT DISTINCT\n" +
                "\t\t\tt1.FID,\n" +
                "\t\t\tt2.fapproval,\n" +
                "\t\t\tt3.fname,\n" +
                "\t\t\tt4.FNAME AS kind,\n" +
                "\t\t\tt4.fparentid\n" +
                "\t\tFROM\n" +
                "\t\t\tt_src_project t1\n" +
                "\t\t\tLEFT JOIN t_src_contractentry t2 ON t1.FID = t2.FID\n" +
                "\t\t\tLEFT JOIN zmee_eip.t_bd_project t3 ON t2.fapproval = t3.FID\n" +
                "\t\t\tLEFT JOIN zmee_eip.t_bd_projectkind t4 ON t3.FKINDID = t4.FID \n" +
                "\t\t) t9 ON t1.FID = t9.FID\n" +
                "\t\tLEFT JOIN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt1.FID,\n" +
                "\t\t\tt2.FBILLNO AS zmee_contractno,\n" +
                "\t\t\tt2.FSIGNDATE AS zmee_signdate,\n" +
                "\t\t\tt3.FORIGINALOFTAXAMOUNT AS zmee_originaloftaxamount,\n" +
                "\t\t\tCONCAT( t2.FSIGNDATE, '-', t2.fk_zmee_dateeas ) AS zmee_dateeas,\n" +
                "\t\t\t( CASE WHEN t4.FId IS NULL THEN '无' ELSE '有' END ) AS zmee_contac \n" +
                "\t\tFROM\n" +
                "\t\t\tt_src_project t1\n" +
                "\t\t\tLEFT JOIN t_pmct_contract t2 ON t1.FID = t2.fk_zmee_projectid\n" +
                "\t\t\tLEFT JOIN t_pmct_contract_s t3 ON t2.FID = t3.FID\n" +
                "\t\t\tLEFT JOIN tk_zmee_lianxi t4 ON t2.FID = t4.fk_zmee_basedata \n" +
                "\t\tWHERE\n" +
                "\t\t\tt2.FBILLSTATUS = 'C' \n" +
                "\t\t) t10 ON t1.FID = t10.FID WHERE t1.FCURRENTNODE IN (  " +
                "'778748079732982784', '1298550339443858432', '784475812626712576', " +
                "'844664748246060032', '1291998834741779456' ) AND t1.FOPENSTATUS IN ( '9', 'A' ) AND t2.FBILLSTATUS = 'C' AND t1.FORGID " +
                "= ? " + timeWhere + "GROUP BY t1.FID ORDER BY t2.FAUDITDATE";
        return DB.queryDataSet(this.getClass().getName(), DBRoute.of("scm"), sql, params.toArray());
    }

    private String formattedDateToyyMMdd(Date date) {
        SimpleDateFormat sdf = new SimpleDateFormat("yy-MM-dd");
        return sdf.format(date);
    }
}
